package DataAccess;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
public class DataProvider {

	 /**
	    * Thuộc tính đối tượng kết nối cơ sở dữ liệu
	    */
	   private  Connection connection;
	   private static DataProvider instance;
	   
//	   public static String connectionString="jdbc:mysql://localhost:3307/quocphu04?autoReconnect=true";
//	   public static String userName="quocphu04";
//	   public static String password="123123";
	   
	   private static String connectionString="jdbc:mysql://localhost:3306/MES_OutDoor";
	   private static String userName="root";
	   private static String password="1";
//	   private static String connectionString="";
//	   private static String userName="";
//	   private static String password="";
	   /**
	    * Phương thức cung cấp đối tượng kết nối đến cơ sở dữ liệu
	    * @return đối tượng kết nối
	    */
	   public Connection getConnection() {
	      return connection;
	   }
	   public static DataProvider getInstance(){
		try {
			if (instance == null) {
				instance = new DataProvider();
				instance.open();
				return instance;
			} else if (instance.connection.isClosed()) {
				instance.open();				
			}
			return instance;

		} catch (SQLException e) {
			System.out.println("Khong get instance dataprovider dc\n"+e.getMessage());
			return null;
		}
	   }
	   /**
	    * Mở kết nối đến cơ sở dữ liệu
	 * @throws SQLException 
	    */
	   public  void open() throws SQLException {		   
	     
	         DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
	         Properties pros = new Properties();
	         pros.setProperty("characterEncoding", "utf8");
	         
//	         String connectionString = "jdbc:mysql://localhost:3306/MES_OutDoor";
//	         pros.setProperty("user", "root");
//	         pros.setProperty("password", "1");
	         
	         //String connectionString = "jdbc:mysql://localhost:3307/quocphu02?autoReconnect=true";     
//	         String connectionString = "jdbc:mysql://s156.eatj.com:3307/quocphu02?autoReconnect=true";
//	         pros.setProperty("user", "quocphu02");
//	         pros.setProperty("password", "1231223");
	         
	         pros.setProperty("user", userName);
	         pros.setProperty("password", password);
	         
	         connection = DriverManager.getConnection(connectionString, pros);
	     
	   }

	   /**
	    * Đóng kết nối cơ sở dữ liệu
	    */
	   public void close() {
	      try {
	         this.connection.close();
	      } catch (SQLException ex) {
	         Logger.getLogger(DataProvider.class.getName()).log(Level.SEVERE, null, ex);
	      }
	   }

	   /**
	    * Rút trích dữ liệu
	    * @param sql câu truy vấn SELECT
	    * @return ResultSet
	    */
	   public ResultSet executeQuery(String sql) {
	      ResultSet rs = null;
	      try {

	         Statement sm = this.connection.createStatement();
	         rs = sm.executeQuery(sql);
	      } catch (SQLException ex) {
	         System.out.println(ex.getMessage());
	      }
	      return rs;
	   }

	   /**
	    * Thêm, xóa, cập nhật dữ liệu
	    * @param sql câu lệnh UPDATE, INSERT, DELETE
	    * @return số lượng dòng cập nhật/thêm/xóa được , -1 : thất bại
	    * @exception SQLException
	    */
	   public int executeUpdate(String sql) {
	      int num = -1;
	      try {
	         Statement sm = this.connection.createStatement();
	         num = sm.executeUpdate(sql);
	      } catch (SQLException ex) {
	        System.out.println(ex.getMessage());
	      }
	      return num;
	   }
	   
	   ////////////////////////////////////
	   public ResultSet executeStore(String store) {
	      ResultSet rs = null;
	      try {
	          CallableStatement state=this.connection.prepareCall(store); 
	         rs=state.executeQuery();
	      } catch (SQLException ex) {
	         System.out.println(ex.getMessage());
	      }
	      return rs;
	   }
	   /////////////////////////////////////////
	   
	   public ResultSet executeStore(String spName,String[] input) {
		   
	      ResultSet rs = null;
	      try {
	         CallableStatement state;
	         if(input.length==0){
	        	 state=this.connection.prepareCall(spName);
	             rs=state.executeQuery();
	         }
	         else{
				String sql = "{CALL " + spName +"(";
				for (int i = 0; i < input.length - 1; i++) {
					sql += "?,";
				}
				sql+= "?)}";
				state=this.connection.prepareCall(sql);
	             for(int i=0;i<input.length;i++){
	                 state.setString(i+1,input[i]);
	             }
	         }
	         rs=state.executeQuery();
	      } catch (SQLException ex) {
	         System.out.println(ex.getMessage());
	      }
	      return rs;
	   }
	   
	    public int execPrepareUpdate(String sql,String[] input) {
	      int num = -1;
	      try {
	         PreparedStatement pS=this.connection.prepareStatement(sql);
	         if(input.length>0){
	             for(int i=0;i<input.length;i++){
	                 pS.setString(i+1,input[i]);
	             }
	             num=pS.executeUpdate();
	             if(num!=-1){
	                 System.out.println("Thành công.");
	             }
	             else{
	                 System.out.println("Thất bại.");
	             }
	         }       
	      } catch (SQLException ex) {
	        System.out.println(ex.getMessage());
	      }
	      return num;
	   }
	   //select
	   
	   //////////
	   public ResultSet execPrepareQuery(String sql,String[] input) {
	      ResultSet r=null;
	      try {
	         PreparedStatement pS=this.connection.prepareStatement(sql);
	         System.out.print("input "+input.length);
	         if(input.length>0){
	             
	             for(int i=0;i<input.length;i++){
	                 pS.setString(i+1,input[i]);
	             }
	             pS.execute();
	            r=pS.getResultSet();
	         }       
	      } catch (SQLException ex) {
	        System.out.println(ex.getMessage());
	      }
	      return r;
	   }
	   
	    public int executeUpdateStore(String store) {
	      int num = -1;
	      try {
	          CallableStatement state=this.connection.prepareCall(store);
	          num=state.executeUpdate();
	          if(num!=-1){
	              System.out.println("");
	          }
	          else{
	              
	          }
	      } catch (SQLException ex) {
	        System.out.println(ex.getMessage());
	      }
	      return num;
	   }
	    public static void init(String connectionString, String userName, String password) throws SQLException{
	    	DataProvider.connectionString=connectionString;
	    	DataProvider.userName=userName;
	    	DataProvider.password=password;
	    	
	    	if (instance != null && instance.connection!=null) {
	    		if( !instance.connection.isClosed()){
	    			instance.connection.close();
	    		}
	    	}else{	
				instance = new DataProvider();
				
			} 
	    	instance.open();
	    }
}
